package edu.up.controle;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import edu.up.beans.VendasBean;
import edu.up.connection.ConnectionDB;

public class Vendas {
	public Vendas() {
		System.out.println( "Construtor Vendas" );
	}
	
	public ConnectionDB con = new ConnectionDB();

	public boolean verificaDados()
	{
		Connection cn = null;
		Statement st  = null;
		ResultSet rs  = null;
		
		int count = 0;
		
		try {
			cn = con.connect();
		
		
		String sql = "select count(*) as res from vendas";
		
		//Buscar Alunos
		st = cn.createStatement();
		rs = st.executeQuery( sql );
		
		while ( rs.next() ) {
			count =  Integer.valueOf(rs.getString( "res" )) ;
			
		}
		
		//return id;
		System.out.println( sql );
		
		cn.close();
		
		if (count > 0)
			return true;
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return false;
	}
	
	
	public int pegaUltimoId()
	{
		Connection cn = null;
		Statement st  = null;
		ResultSet rs  = null;
		
		int id = 0;
		
		boolean verifica = verificaDados();
		
		if (verifica)
		{
			try {
				cn = con.connect();
			
			
			String sql = "select MAX(id_vendas) as id from vendas";
			
			//Buscar Alunos
			st = cn.createStatement();
			rs = st.executeQuery( sql );
			
			while ( rs.next() ) {
				id =  Integer.valueOf(rs.getString( "id" )) ;
				
			}
			
			//return id;
			System.out.println( sql );
			cn.close();
			
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		else
			id=0;
		
		return id;
	}
	
	public int gravar(VendasBean bean) 
	{	
		System.out.println( "GRAVA CLIENTE!" );
		
		Connection cn = null;
		Statement st  = null;
		ResultSet rs  = null;
		int id = pegaUltimoId();
		//Antes de executar o programa execute o startup.bat em linha de comando DOS
		try {
			cn = con.connect();
		
		id+=1;
		String sql = "insert into cliente (id_vendas, id_user, id_sonho)";
		sql += "values ("+bean.getId_sonho()+","+bean.getId_user()+","+bean.getId_venda()+")";
		
		//Buscar Alunos
		st = cn.createStatement();
		rs = st.executeQuery( sql );
		
		System.out.println( sql );
		
		sql = "commit";
		st = cn.createStatement();
		rs = st.executeQuery( sql );
		cn.close();
		
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
		return 1;
	}
	
	
	
	
	public int alterar(VendasBean bean) 
	{	
		System.out.println( "ALTERAR CLIENTE!" );
		
		Connection cn = null;
		Statement st  = null;
		ResultSet rs  = null;
		
		//Antes de executar o programa execute o startup.bat em linha de comando DOS
		try {
			cn = con.connect();
		
			
		String sql = "update cliente set " +
				"id_user = '" + bean.getId_user()+"'"+
				",id_sonho = '" + bean.getId_sonho()+"'"+
				"where id = " + bean.getId_venda();
		//Buscar Alunos
		st = cn.createStatement();
		rs = st.executeQuery( sql );
		
		System.out.println( sql );
		sql = "commit";
		st = cn.createStatement();
		rs = st.executeQuery( sql );
		cn.close();
		
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
		return 1;
	}
	
	public int excluir(int id) 
	{	
		System.out.println( "EXCLUIR CLIENTE!" );
		
		Connection cn = null;
		Statement st  = null;
		ResultSet rs  = null;
		
		//Antes de executar o programa execute o startup.bat em linha de comando DOS
		try {
			cn = con.connect();
		
			
			String sql = "delete from sonhos "+
				"where id = " + id;
		//Buscar Alunos
		st = cn.createStatement();
		rs = st.executeQuery( sql );
		
		System.out.println( sql );
		sql = "commit";
		st = cn.createStatement();
		rs = st.executeQuery( sql );
		cn.close();
		
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
		return 1;
	}
	
	
	
	public ArrayList<VendasBean> listaVendas()
	{
		Connection cn = null;
		Statement st  = null;
		ResultSet rs  = null;
		
		String nome = null;
		
		ArrayList<VendasBean> lista = new ArrayList<VendasBean>();
		
		try {
			cn = con.connect();
		
		
		String sql = "select id_vendas as id," +
				"id_user as user," +
				"id_sonho as sonho," +
				"from vendas";
		
		//Buscar Alunos
		st = cn.createStatement();
		rs = st.executeQuery( sql );
		
		while ( rs.next() ) {
			VendasBean bean = new VendasBean();
			bean.setId_venda( Integer.valueOf(rs.getString( "id" ))) ;
			bean.setId_user( Integer.valueOf(rs.getString( "user" ))) ;
			bean.setId_sonho( Integer.valueOf(rs.getString( "sonho" ))) ;
			
			
			lista.add(bean);
			//bean.setLista(lista);
		}
		
		//return id;
		System.out.println( sql );
		cn.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return lista;
	}
}
